from django.db.models import Count, Q, Min, Max, Sum, Avg
import numpy as np
from job import models
import operator


def chart_data():
    read_data = models.JobC.objects.values('location').annotate(num=Count('location'))
    # 所有城市职位数city
    # 得到各城市职业数，数据格式：{city:value}
    city = {}
    for i in read_data.all():
        j = i['location'].split('-')[0]
        if j not in city:
            city[j] = i['num']
        else:
            city[j] += i['num']
    # base_bar_data数据格式：[[city,value],[],..]
    base_bar_data = []
    for i in city:
        data = [i, city[i]]
        base_bar_data.append(data)
    base_bar_data = sorted(base_bar_data, key=operator.itemgetter(1), reverse=True)[:20]

    # 可下钻柱状图/饼状一层/可带图例 数据格式：[{'name':city,'y':value,'drilldown':city},...]
    bar_data = []
    for i in base_bar_data:
        data = {'name': i[0], 'y': i[1], 'drilldown': i[0]}
        bar_data.append(data)

    # 数据格式：[{'city':city,'value':value},...]
    result_map = []
    for i in city:
        data = {'city': i, 'value': city[i]}
        result_map.append(data)

    # 可下钻饼图二层/可带图例 数据格式[{'type':'pie','id',city,'data':[['area',value],...]},...]
    pie_data = []
    # for i in base_bar_data:
    #     area = []
    #     for j in read_data.all():
    #         if j['location'].split('-')[0] == i[0]:
    #             area_data = [j['location'],j['num']]
    #             area.append(area_data)
    #     city_data = {'type':'pie','id':i[0],'data':area}
    #     pie_data.append(city_data)

    # 词云图 数据格式[[positionname,num],...]

    read_data = models.JobC.objects.values('key_word').annotate(num=Count('key_word'))
    wordcloud = []
    for i in read_data:
        data = [i['key_word'], i['num']]
        wordcloud.append(data)
    wordcloud = sorted(wordcloud, key=operator.itemgetter(1), reverse=True)
    # 可下行数据格式[{'name': city, 'y': value, 'drilldown': city}, ...]

    position_num = []
    for i in wordcloud:
        data = {'name': i[0], 'y': i[1], 'drilldown': i[0]}
        position_num.append(data)

    read_data = sorted(models.JobC.objects.values('positionname').annotate(num=Count('positionname')),
                       key=operator.itemgetter('num'), reverse=True)[:20]
    position_detail = []
    for i in read_data:
        data = {'name':i['positionname'],'y':i['num']}
        position_detail.append(data)

    # 各城市在职业中的占比
    key_city = []
    data = models.JobC.objects.values('key_word').annotate(num=Count('key_word'))
    for i in data:
        read_data = models.JobC.objects.filter(Q(key_word__contains=i['key_word'])).values('area', 'key_word').annotate(
            num=Count('key_word'))
        L = []
        for j in read_data:
            L.append([j['area'], j['num']])
        data = {'type': 'pie', 'id': i['key_word'], 'data': L[:10]}
        key_city.append(data)

    # 简单柱状带图例 数据格式 {'name':i[0],'data':[{'name':i[0],'y':i[1]}]}
    min = models.JobC.objects.values('key_word').annotate(min_avg=Avg('salary_min'))
    max = models.JobC.objects.values('key_word').annotate(max_avg=Avg('salary_max'))
    min_avg = [round(x['min_avg'], 2) for x in min]
    max_avg = [round(y['max_avg'], 2) for y in max]
    z = list(zip(min_avg, max_avg))
    avg_list = list(np.mean(z, 1))
    salary_temp = []
    for i in range(len(avg_list)):
        data = [min[i]['key_word'], avg_list[i]]
        salary_temp.append(data)
    salary_temp = sorted(salary_temp, key=operator.itemgetter(1), reverse=True)
    salary_avg = []
    for i in salary_temp:
        data = {'name': i[0],
                'data': [{'name': i[0], 'y': i[1], 'drilldown': i[0]}]}
        salary_avg.append(data)

    key_salary = []
    data = models.JobC.objects.values('key_word').annotate(num=Count('key_word'))
    for i in data:
        read_data = models.JobC.objects.filter(Q(key_word__contains=i['key_word'])).values('salary',
                                                                                          'key_word').annotate(
            num=Count('key_word'))
        read_data = sorted(read_data, key=operator.itemgetter('num'), reverse=True)
        print(read_data)
        L = []
        for j in read_data:
            L.append([j['salary'], j['num']])
        data = {'name': i['key_word'], 'id': i['key_word'], 'data': L[:10]}
        key_salary.append(data)
    # key_exp = []
    # data = models.JobC.objects.values('key_word').annotate(num=Count('key_word'))
    # for i in data:
    #     read_data = models.JobC.objects.filter(Q(key_word__contains=i['key_word'])).values('work_exp').annotate(
    #         num=Count('work_exp'))
    #     L = []
    #     for j in read_data:
    #         L.append([j['work_exp'], j['num']])
    #     data = {'type': 'pie', 'id': i['key_word'], 'data': L[:10]}
    #     key_exp.append(data)

    result = [bar_data, pie_data, wordcloud, position_num, key_city, salary_avg, key_salary, position_detail]
    return result
